package com.cs.excelreadimport.easyexcel.service.impl;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.cs.excelreadimport.easyexcel.constants.ExcelConstants;
import com.cs.excelreadimport.easyexcel.constants.Page;
import com.cs.excelreadimport.easyexcel.entity.SysUser;
import com.cs.excelreadimport.easyexcel.mapper.SysUserMapper;
import com.cs.excelreadimport.easyexcel.service.ExcelService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeansException;
import org.springframework.stereotype.Service;
import org.springframework.util.StopWatch;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.*;

@Service
@Slf4j
public class ExcelServiceImpl implements ExcelService {

    @Resource
    private SysUserMapper sysUserMapper;

    private static  StopWatch stopWatch = new StopWatch();
    /*
     * @Description: 单线程
     * @return void
     * @Author: cs
     * @Date: 2024/11/19 15:37
     */
    @Override
    public void singleExport() throws IOException {
        OutputStream outputStream =null;
        stopWatch.start();
        try {
            //记录总数:实际中需要根据查询条件进行统计即可
            //LambdaQueryWrapper<Emp> lambdaQueryWrapper = new QueryWrapper<Emp>().lambda().eq(Emp::getEmpno, 1000001);
            Integer totalCount = sysUserMapper.selectCount();
            //每一个Sheet存放100w条数据
            Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
            //每次写入的数据量20w,每页查询20W
            Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
            //计算需要的Sheet数量
            Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
            //计算最后一个sheet需要写入的次数
            Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

            ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = requestAttributes.getResponse();
            outputStream = response.getOutputStream();
            //必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();

            //开始分批查询分次写入
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetName("测试Sheet1"+i);
                sheet.setSheetNo(i);
                //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    //分页查询一次20w
                    Page pageTmp = new Page(j + 1 + oneSheetWriteCount * i, writeDataRows);
                    List<SysUser> sysUserList = sysUserMapper.selectPage(pageTmp.getOffset(),pageTmp.getPageSize());
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, "员工信息" + (i + 1)).head(SysUser.class)
                            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                    //写数据
                    excelWriter.write(sysUserList, writeSheet);
                }
            }

            //结束计时
            stopWatch.stop();
            log.info("将数据到入excel耗时：" + stopWatch.getTotalTimeSeconds()+"S");

            // 下载EXCEL
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止浏览器端导出excel文件名中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("员工信息", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BeansException e) {
            e.printStackTrace();
        }finally {
            if (outputStream != null) {
                outputStream.close();
            }
        }
    }

    /*
     * @Description: 多线程去查询数据并写入excel 有下面几种方式：
     * 1. 将100w条数据分成5个sheet去写入一个sheet存放数据20w
     *    创建一个类继承runnable接口 去实现导入到一个sheet的逻辑
     *    主程序按照sheet个数循环创建任务放入线程池里面
     * 2. 多线程异步并行：
     *    2.1 根据CompletableFuture的runAsync去执行查询写入excel的逻辑
     *        但是这样有一个缺点就是每个线程查询结果的时间不一样可能导致写入excel的数据顺序不一致
     *    2.2 使用Future去实现数据顺序的一致性，内存消耗会大一点 => 空间换时间，而且future.get也会消耗时间
     * @Author: cs
     * @Date: 2024/11/19 15:36
     * 保证一致性
     */
    @Override
    public void batchExportWithOrder() throws IOException {
        OutputStream outputStream = null;
        //开始计时
        stopWatch.start();
        try {
            //记录总数:实际中需要根据查询条件进行统计即可
            Integer totalCount = sysUserMapper.selectCount();
            //每一个Sheet存放100w条数据
            Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
            //每次写入的数据量20w,每页查询20W
            Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
            //计算需要的Sheet数量
            Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
            //计算最后一个sheet需要写入的次数
            Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

            ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = requestAttributes.getResponse();
            outputStream = response.getOutputStream();
            // 必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();

            // 创建线程池
            ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());

            // 创建Future列表
            List<Future<List<SysUser>>> futures = new ArrayList<>();

            // 遍历每一个Sheet
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetName("测试Sheet1"+i);
                sheet.setSheetNo(i);
                //创建Future列表
                for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    int finalI = j;
                    Future<List<SysUser>> future = executorService.submit(() -> {
                        try {
                            // 分页查询一次20w
                            int offset = finalI * writeDataRows;
                            return sysUserMapper.selectPage(offset, writeDataRows);
                        } catch (Exception e) {
                            e.printStackTrace();
                            return null;
                        }
                    });
                    futures.add(future);
                }
                // 按顺序从Future列表中获取数据并写入Excel
                for (Future<List<SysUser>> future : futures) {
                    List<SysUser> sysUserList = future.get(); // 阻塞等待结果
                    if (sysUserList != null) {
                        WriteSheet writeSheet = EasyExcel.writerSheet(i, "员工信息" ).head(SysUser.class)
                                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                        excelWriter.write(sysUserList, writeSheet);
                    }
                }
            }

            //结束计时
            stopWatch.stop();
            log.info("导出到excel耗时："+ stopWatch.getTotalTimeSeconds()+"S");


            // 下载EXCEL
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止浏览器端导出excel文件名中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("员工信息", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
            outputStream.flush();
        } catch (IOException | InterruptedException | ExecutionException | BeansException e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    /*
     * @Description: 多线程异步并行 不保证一致性
     * @Author: cs
     * @Date: 2024/11/19 16:52
     */
    @Override
    public void batchExportWithNotOrder() throws IOException {
        OutputStream outputStream = null;
        //开始计时
        stopWatch.start();
        try {
            //记录总数:实际中需要根据查询条件进行统计即可
            Integer totalCount = sysUserMapper.selectCount();
            //每一个Sheet存放100w条数据
            Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
            //每次写入的数据量20w,每页查询20W
            Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
            //计算需要的Sheet数量
            Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
            //计算最后一个sheet需要写入的次数
            Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

            ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = requestAttributes.getResponse();
            outputStream = response.getOutputStream();
            // 必须放到循环外，否则会刷新流
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();

            // 创建线程池
            ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());

            // 使用CompletableFuture并行处理每个批次的数据
            List<CompletableFuture<Void>> futures = new java.util.ArrayList<>();

            // 遍历每个Sheet,每个Sheet写入一次
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetName("测试Sheet1" + i);
                sheet.setSheetNo(i);
                //创建Future列表
                for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    int finalI = j;
                    int tmpI = i;
                    CompletableFuture<Void> future = CompletableFuture.runAsync(() -> {
                        try {
                            // 分页查询一次20w
                            int offset = finalI * writeDataRows;
                            List<SysUser> sysUserList = sysUserMapper.selectPage(offset, writeDataRows);
                            //数据写入
                            WriteSheet writeSheet = EasyExcel.writerSheet(tmpI, "员工信息" ).head(SysUser.class)
                                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();

                            //加锁防止造成数据混乱
                            synchronized (excelWriter) {
                                excelWriter.write(sysUserList, sheet);
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }, executorService);
                    futures.add(future);
                }
            }

            // 等待所有任务完成
            CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])).join();

            //结束计时
            stopWatch.stop();
            log.info("导出到excel耗时："+ stopWatch.getTotalTimeSeconds()+"S");

            // 下载EXCEL
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止浏览器端导出excel文件名中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("员工信息", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
            outputStream.flush();
        } catch (IOException | BeansException e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
